﻿Imports System.Data.SqlClient
Imports System.Data
Imports System.Net.Mail
Imports System.Net
Public Class ViewStudentFromStudentSide
    Inherits System.Web.UI.Page
    'Dim table As New DataTable
    Dim myDS As DataSet
    Dim con As New DBConnect

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'check if user signed in if not going back to login page 
        If Not Session.Item("UserIn").Equals("yes") Then
            Response.Redirect("default.aspx")
        End If
        'hide initially request correction part 
        btnSubmit.Visible = False
        lblMsg.Visible = False
        ddDepartment.Visible = False
        ddOrganization.Visible = False
        ddClassification.Visible = False
        ddEndTerm.Visible = False
        ddRole.Visible = False
        ddStartTerm.Visible = False
        Label1.Visible = False
        Label2.Visible = False
        Label3.Visible = False
        Label4.Visible = False
        Label5.Visible = False
        Label6.Visible = False
        lblMsg2.Visible = False
        txtComments.Visible = False
        'CheckBox1.Visible = False
        'CheckBox2.Visible = False
        RadioButtonList1.Visible = False

        If Not IsPostBack Then
            getTUID()

            ' Set user name and ID in UI
            lblTuID.Text = "ID: " + Session("TUID").ToString
            Dim SqlConnectString As String = con.SqlConnectString
            Dim myConnection As New SqlConnection(SqlConnectString)
            Dim myCommand As New SqlCommand()
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.CommandText = "GetUserName"

            Dim inputParameter As New SqlParameter("@userID", Session("UserID"))
            inputParameter.Direction = ParameterDirection.Input
            myCommand.Parameters.Add(inputParameter)

            'returning value of 0 if not found 
            Dim returnParameter As New SqlParameter("@firstName", 0)
            returnParameter.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(returnParameter)

            Dim ds As DataSet = con.getDataSetUsingCmdObj(myCommand)

            If ds.Tables(0).Rows.Count = 0 Then
                lblName.Visible = False
            Else
                lblName.Text = "Welcome " + ds.Tables(0).Rows(0).Item("firstName").ToString
            End If
        End If


        If CStr(Session.Item("error")).Equals("true") Then
            ClientScript.RegisterStartupScript(Me.GetType, "alert", "alert('Please select one record from table');", True)
            Session("error") = "false"

        End If

    End Sub

    ' Gets and Sets the current state of SortDirection
    Public Property SortDirection() As SortDirection
        Get
            If ViewState("SortDirection") Is Nothing Then
                ViewState("SortDirection") = SortDirection.Descending
            End If
            Return DirectCast(ViewState("SortDirection"), SortDirection)
        End Get
        Set(value As SortDirection)
            ViewState("SortDirection") = value
        End Set
    End Property

    ' This method will sort the gridview based on which column header is clicked
    Protected Sub SortGridView(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
        Dim mySortExpression As String = e.SortExpression   ' Column to be sorted
        Dim newSortDirection As String = String.Empty       ' Column's sort direction

        ' If the current state SortDirection is Ascending, set it to Descending and vice versa
        ' Then, set the newSortDirection to DESC or ASC based on current SortDirection
        If SortDirection = SortDirection.Ascending Then
            SortDirection = SortDirection.Descending
            newSortDirection = "DESC"
        Else
            SortDirection = SortDirection.Ascending
            newSortDirection = "ASC"
        End If

        ' Get the data from the database
        ' Sort the data, based selected column (mySortExpression), by newSortDirection (ASC/DESC)
        ' Then bind the sorted data to the GV
        myDS = GetStudentData()
        myDS.Tables(0).DefaultView.Sort = mySortExpression & " " & newSortDirection
        gvStudent.DataSource = myDS.Tables(0)
        gvStudent.DataBind()

    End Sub


    Private Sub getTUID()
        Dim SqlConnectString As String = con.SqlConnectString
        Dim myConnection As New SqlConnection(SqlConnectString)
        Dim myCommand As New SqlCommand()
        myCommand.CommandType = CommandType.StoredProcedure
        myCommand.CommandText = "GetTUID"

        Dim inputParameter As New SqlParameter("@userID", Session("UserID"))
        inputParameter.Direction = ParameterDirection.Input
        myCommand.Parameters.Add(inputParameter)

        'returning value of 0 if not found 
        Dim returnParameter As New SqlParameter("@tuID", 0)
        returnParameter.Direction = ParameterDirection.Output
        myCommand.Parameters.Add(returnParameter)

        Session("TUID") = con.ExecScalarFunction(myCommand)

        If Session("TUID") > 0 Then
            gvStudent.DataSource = GetStudentData()
            gvStudent.DataBind()
        ElseIf Session("TUID") = 0 Then
            Response.Redirect("setAccessNet.aspx")
        End If


    End Sub

    Private Function GetStudentData() As DataSet
        ' *** TO-DO *** need to add a WHERE LIKE clause here to reference user's AccessNetID
        'Dim strSql As String = "SELECT d.DepartmentName, o.organizationName, i.classification, i.role, (SELECT t.TermName WHERE i.startTerm = t.TermID) as startTerm, (SELECT t2.TermName WHERE i.endTerm = t2.TermID) as endTerm, u.accessNetID FROM Involvments i INNER JOIN Users u ON i.tuID = u.ID INNER JOIN Departments d ON i.departmentID = d.DepartmentID INNER JOIN Organizations o ON i.organizationID = o.organizationID INNER JOIN Term t on i.startTerm = t.TermID INNER JOIN Term t2 on i.endTerm = t2.TermID;"
        'myDS = con.getDataSet(strSql)

        Dim SqlConnectString As String = con.SqlConnectString
        Dim myConnection As New SqlConnection(SqlConnectString)
        Dim myCommand As New SqlCommand() 
        myCommand.CommandType = CommandType.StoredProcedure
        myCommand.CommandText = "GetStudentData"

        Dim inputParameter As New SqlParameter("@studentID", Session("TUID"))
        inputParameter.Direction = ParameterDirection.Input
        myCommand.Parameters.Add(inputParameter)
        myDS = con.getDataSetUsingCmdObj(myCommand)

        Return myDS
    End Function
    Protected Sub lnkEditRequest_Click(sender As Object, e As EventArgs) Handles lnkEditRequest.Click
        RadioButtonList1.Visible = True
    End Sub

    Protected Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click
        lblMsg2.Visible = True
        btnSubmit.Visible = False
        lblMsg.Visible = False
        ddDepartment.Visible = False
        ddOrganization.Visible = False
        ddClassification.Visible = False
        ddEndTerm.Visible = False
        ddRole.Visible = False
        ddStartTerm.Visible = False
        Label1.Visible = False
        Label2.Visible = False
        Label3.Visible = False
        Label4.Visible = False
        Label5.Visible = False
        Label6.Visible = False
        txtComments.Visible = False
        RadioButtonList1.Visible = False
        'CheckBox1.Visible = False
        'CheckBox2.Visible = False
        lnkEditRequest.Visible = True
        'txtCorrectionRequest.Visible = False


        Dim newExisting As String = CStr(Session.Item("radioButtonValue"))
        Dim emailBody As String
        Dim emailSubject As String

        'NEW RECORD REQUEST 
        If newExisting.Equals("New") Then
            Dim SqlConnectString As String = con.SqlConnectString
            Dim myConnection As New SqlConnection(SqlConnectString)
            Dim myCommand As New SqlCommand()
            myCommand.CommandType = CommandType.StoredProcedure
            myCommand.CommandText = "GetDepartmentsEmail"

            Dim inputParameter As New SqlParameter("@departmentName", ddDepartment.SelectedItem.Text)
            inputParameter.Direction = ParameterDirection.Input
            myCommand.Parameters.Add(inputParameter)
            Dim ds As DataSet = con.getDataSetUsingCmdObj(myCommand)

            'sending email
            'emailBody = "Please add this Record: " + ddDepartment.SelectedItem.Text + ", " + ddOrganization.SelectedItem.Text + ", " + ddClassification.SelectedItem.Text + ", " + ddRole.Text + ", " + ddStartTerm.Text + ", " + ddEndTerm.Text + ", " + ", " + " COMMENTS: " + " " + txtComments.Text
            Dim tuID As String = lblTuID.Text
            emailSubject = "SATS: Student's New Record Request"
            Using mm As New MailMessage("satstemple@gmail.com", ds.Tables(0).Rows(0)(0))
                'Dim toEmail As String ' get admin's email from department
                mm.Subject = emailSubject
                mm.Body = "Please add following record: - <br/>" + "TU ID - " + tuID + "<br/>" + "Department - " + ddDepartment.SelectedItem.Text + "<br/>" + "Organization - " + ddOrganization.SelectedItem.Text + "<br/>" + "Classification - " + ddClassification.SelectedItem.Text + "<br/>" + "Role - " + ddRole.Text + "<br/>" + "Start Term - " + ddStartTerm.Text + "<br/>" + "End Term - " + ddEndTerm.Text + "<br/>" + "Comments - " + txtComments.Text + "<br/>" + "<hr/>" 'emailBody
                mm.IsBodyHtml = True
                Dim smtp As New SmtpClient()
                smtp.Host = "smtp.gmail.com"
                smtp.EnableSsl = True
                Dim NetworkCred As New NetworkCredential("satstemple@gmail.com", "BaT58teR")
                smtp.UseDefaultCredentials = True
                smtp.Credentials = NetworkCred
                smtp.Port = 587
                smtp.Send(mm)
                lblMsg2.Visible = True
                ClientScript.RegisterStartupScript(Me.GetType, "alert", "alert('Email sent.');", True)
            End Using
        Else
            'Getting Records
            'Dim dt As New DataTable()
            Dim i As Integer = 0
            'cheching if more than one record selected
            For Each row As GridViewRow In gvStudent.Rows
                If row.RowType = DataControlRowType.DataRow Then
                    Dim chkRow As CheckBox = TryCast(row.Cells(6).FindControl("CheckBox1"), CheckBox)
                    If chkRow.Checked Then
                        i = i + 1
                    End If
                End If
            Next
            ' making sure user select one record only 
            If i = 0 Or i > 1 Then
                lblMsg.Text = "Please select one record from table"
                lblMsg.Visible = True
                Session("error") = "true"
                Session("Comments") = txtComments.Text
                ClientScript.RegisterStartupScript(Me.GetType, "alert", "alert('Please select one record from table');", True)
                Response.Redirect("ViewStudentFromStudentSide.aspx")
            ElseIf i = 1 Then
                'EXISTING RECROD REQUEST 
                'Else
                ' dt.Columns.AddRange(New DataColumn(5) {New DataColumn("Department"), New DataColumn("Organization"), New DataColumn("Classification"), New DataColumn("Role"), New DataColumn("Start Term"), New DataColumn("End Term")})
                For Each row As GridViewRow In gvStudent.Rows
                    If row.RowType = DataControlRowType.DataRow Then
                        Dim chkRow As CheckBox = TryCast(row.Cells(6).FindControl("CheckBox1"), CheckBox)
                        If chkRow.Checked Then
                            Dim Department As String = row.Cells(0).Text
                            Dim Organization As String = row.Cells(1).Text
                            Dim Classification As String = row.Cells(2).Text
                            Dim Role As String = row.Cells(3).Text
                            Dim StartTerm As String = row.Cells(4).Text
                            Dim EndTerm As String = row.Cells(5).Text
                            ' dt.Rows.Add(Department, Organization, Classification, Role, StartTerm, EndTerm)
                            Session("emailBody") = "Department - " + Department + "<br/>" + "Organization - " + Organization + "<br/>" + "Classification - " + Classification + "<br/>" + "Role - " + Role + "<br/>" + "Start Term - " + StartTerm + "<br/>" + "End Term - " + EndTerm + "<br/>"
                            Session("department") = Department
                            i = 1
                        Else

                        End If
                    End If
                Next
                'getting departments admin email 
                Dim SqlConnectString As String = con.SqlConnectString
                Dim myConnection As New SqlConnection(SqlConnectString)
                Dim myCommand As New SqlCommand()
                myCommand.CommandType = CommandType.StoredProcedure
                myCommand.CommandText = "GetDepartmentsEmail"
                Dim department0 As String = CStr(Session.Item("department"))
                Dim inputParameter As New SqlParameter("@departmentName", department0)
                inputParameter.Direction = ParameterDirection.Input
                myCommand.Parameters.Add(inputParameter)
                Dim ds As DataSet = con.getDataSetUsingCmdObj(myCommand)
                'sending email
                Dim tuID As String = lblTuID.Text
                '  emailBody = "Please update this Record: " + " " + CStr(Session.Item("emailBody")) + ", " + ", COMMENTS: " + " " + txtComments.Text
                emailBody = "Please update following record: <br/>" + "TU " + tuID + "<br/>" + Session.Item("emailBody") + "Comments - " + txtComments.Text + "<br/>" + "<hr/>"
                emailSubject = "SATS: Student's Current Record Update Request"
                Using mm As New MailMessage("satstemple@gmail.com", ds.Tables(0).Rows(0)(0))
                    'Dim toEmail As String ' get admin's email from department
                    mm.Subject = emailSubject
                    mm.Body = emailBody
                    mm.IsBodyHtml = True
                    Dim smtp As New SmtpClient()
                    smtp.Host = "smtp.gmail.com"
                    smtp.EnableSsl = True
                    Dim NetworkCred As New NetworkCredential("satstemple@gmail.com", "BaT58teR")
                    smtp.UseDefaultCredentials = True
                    smtp.Credentials = NetworkCred
                    smtp.Port = 587
                    smtp.Send(mm)
                    lblMsg2.Visible = True
                    ClientScript.RegisterStartupScript(Me.GetType, "alert", "alert('Email sent!');", True)
                End Using
                'End If
            End If
        End If
    End Sub

    Protected Sub RadioButtonList1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles RadioButtonList1.SelectedIndexChanged
        If RadioButtonList1.SelectedValue.Equals("0") Then
            Session("radioButtonValue") = "New"
            'dispaying tools to request corrections
            lnkEditRequest.Visible = False
            btnSubmit.Visible = True
            lblMsg.Visible = True

            ddDepartment.Visible = True
            ddOrganization.Visible = True
            ddClassification.Visible = True
            ddEndTerm.Visible = True
            ddRole.Visible = True
            ddStartTerm.Visible = True
            Label1.Visible = True
            Label2.Visible = True
            Label3.Visible = True
            Label4.Visible = True
            Label5.Visible = True
            Label6.Visible = True
            txtComments.Visible = True
            'CheckBox1.Visible = True
            'CheckBox2.Visible = True
        Else
            Session("radioButtonValue") = "Existing"
            btnSubmit.Visible = True
            txtComments.Text = "Comments"
            txtComments.Visible = True
            lblMsg.Text = "Please check one record you wish to update and then type your comments below"
            lblMsg.Visible = True
        End If
    End Sub
End Class